﻿Imports System.Data
Imports System.Data.SqlClient
Imports CondorTravel.BE
Imports CondorTravelUTIL.DL




Public Class PaqueteDALC

    Public Function ListaPaqueteBusqueda(ByVal objPqtBE As PaquetesBE) As List(Of PaquetesBE)

        Dim sqlListar As String
        Dim objListaPaquete As New List(Of PaquetesBE)
        Dim objPaqueteBE As PaquetesBE = Nothing
        Dim arrSqlParameter(5) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try

            sqlListar = "SP_PaqueteList"

            arrSqlParameter(0) = New SqlParameter("@T_DESCRIPCION", System.Data.SqlDbType.VarChar)
            arrSqlParameter(0).Value = objPqtBE.NombrePaquete

            arrSqlParameter(1) = New SqlParameter("@C_COD_SERVICIO", System.Data.SqlDbType.VarChar)
            arrSqlParameter(1).Value = objPqtBE.Servicio.CodServicio

            arrSqlParameter(2) = New SqlParameter("@C_COD_PROVEEDOR", System.Data.SqlDbType.VarChar)
            arrSqlParameter(2).Value = objPqtBE.Proveedor.Codigo

            arrSqlParameter(3) = New SqlParameter("@T_ESTADO", System.Data.SqlDbType.VarChar)
            arrSqlParameter(3).Value = objPqtBE.Estado

            arrSqlParameter(4) = New SqlParameter("@F_INI_VIGENCIA", System.Data.SqlDbType.VarChar)
            arrSqlParameter(4).Value = objPqtBE.InicioVigencia

            arrSqlParameter(5) = New SqlParameter("@F_FIN_VIGENCIA", System.Data.SqlDbType.VarChar)
            arrSqlParameter(5).Value = objPqtBE.FinVigencia

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)


            objListaPaquete = New List(Of PaquetesBE)()

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objPaqueteBE = New PaquetesBE()
                    objPaqueteBE.codPaquete = dt.Rows(intI).ItemArray(0).ToString
                    objPaqueteBE.NombrePaquete = dt.Rows(intI).ItemArray(1).ToString
                    objPaqueteBE.InicioVigencia = dt.Rows(intI).ItemArray(2).ToString
                    objPaqueteBE.FinVigencia = dt.Rows(intI).ItemArray(3).ToString
                    objPaqueteBE.Estado = dt.Rows(intI).ItemArray(4).ToString
                    intI = intI + 1

                    objListaPaquete.Add(objPaqueteBE)
                End While
            End If

        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objListaPaquete
    End Function


    Public Function InsertarPaquete(ByVal objPaqueteBE As PaquetesBE, ByRef bExito As Boolean, ByRef sMensaje As String, ByRef sCodigo As String) As Boolean

        Dim arrSqlParameter(6) As SqlParameter

        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@C_COD_SOL_PAQUETE"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objPaqueteBE.codSolicitud

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@T_DESCRIPCION"
            arrSqlParameter(1).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(1).Size = 50
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objPaqueteBE.NombrePaquete

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@F_INI_VIGENCIA"
            arrSqlParameter(2).SqlDbType = SqlDbType.Date
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objPaqueteBE.InicioVigencia

            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@F_FIN_VIGENCIA"
            arrSqlParameter(3).SqlDbType = SqlDbType.Date
            arrSqlParameter(3).Direction = ParameterDirection.Input
            arrSqlParameter(3).Value = objPaqueteBE.FinVigencia

            arrSqlParameter(4) = New SqlParameter()
            arrSqlParameter(4).ParameterName = "@T_ESTADO"
            arrSqlParameter(4).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(4).Size = 20
            arrSqlParameter(4).Direction = ParameterDirection.Input
            arrSqlParameter(4).Value = objPaqueteBE.Estado

            arrSqlParameter(5) = New SqlParameter()
            arrSqlParameter(5).ParameterName = "@USUARIO"
            arrSqlParameter(5).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(5).Size = 10
            arrSqlParameter(5).Direction = ParameterDirection.Input
            arrSqlParameter(5).Value = objPaqueteBE.UsuarioCreacion

            arrSqlParameter(6) = New SqlParameter()
            arrSqlParameter(6).ParameterName = "@COD_PAQUETE"
            arrSqlParameter(6).SqlDbType = SqlDbType.Int
            arrSqlParameter(6).Direction = ParameterDirection.Output
            arrSqlParameter(6).Value = objPaqueteBE.codPaquete

            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("SP_PaqueteIns", bExito, sMensaje, arrSqlParameter)
            sCodigo = objPaqueteBE.codPaquete
            Return bExito

        Catch ex As Exception
            Throw
        End Try

    End Function

    Public Function InsertarPaqueteDetalle(ByVal objPaqueteDetBE As DetallePaqueteBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(2) As SqlParameter

        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@C_COD_PAQUETE"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objPaqueteDetBE.C_COD_PAQUETE

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@C_COD_SERVICIO"
            arrSqlParameter(1).SqlDbType = SqlDbType.Int
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objPaqueteDetBE.C_COD_SERVICIO

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@C_COD_CIUDAD"
            arrSqlParameter(2).SqlDbType = SqlDbType.Int
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objPaqueteDetBE.C_COD_CIUDAD


            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("SP_PaqueteDetalleIns", bExito, sMensaje, arrSqlParameter)

            Return bExito

        Catch ex As Exception
            Throw
        End Try

    End Function


    Public Function ListaDtsPaquete(ByVal objPaqueteBE As PaquetesBE) As DataSet


        Dim sqlListar As String
        Dim arrSqlParameter(3) As SqlParameter
        Dim ds As DataSet

        Dim objDatos As New AccesoDatos()
            Dim blnExito As Boolean
            Dim strMensaje As String = String.Empty
            Dim intI As Integer = 0



        sqlListar = "SP_PaqueteRptList"

            Try

            arrSqlParameter(0) = New SqlParameter("@DESPAQ", System.Data.SqlDbType.VarChar)
            arrSqlParameter(0).Value = objPaqueteBE.NombrePaquete


            arrSqlParameter(1) = New SqlParameter("@FECINI", System.Data.SqlDbType.VarChar)
            arrSqlParameter(1).Value = objPaqueteBE.FechaInicio

            arrSqlParameter(2) = New SqlParameter("@FECFIN", System.Data.SqlDbType.VarChar)
            arrSqlParameter(2).Value = objPaqueteBE.FechaFinal

            arrSqlParameter(3) = New SqlParameter("@CODEST", System.Data.SqlDbType.VarChar)
            arrSqlParameter(3).Value = objPaqueteBE.Estado

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return ds
    End Function

    Public Function ListaPaquete(ByVal objPaqueteBE As PaquetesBE) As List(Of PaquetesBE)

        Dim sqlListar As String
        Dim objListaPaquete As New List(Of PaquetesBE)
        Dim objPqtBE As PaquetesBE = Nothing
        Dim arrSqlParameter(3) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try

            sqlListar = "SP_PaqueteRptList"

            arrSqlParameter(0) = New SqlParameter("@DESPAQ", System.Data.SqlDbType.VarChar)
            arrSqlParameter(0).Value = objPaqueteBE.NombrePaquete


            arrSqlParameter(1) = New SqlParameter("@FECINI", System.Data.SqlDbType.VarChar)
            arrSqlParameter(1).Value = objPaqueteBE.FechaInicio

            arrSqlParameter(2) = New SqlParameter("@FECFIN", System.Data.SqlDbType.VarChar)
            arrSqlParameter(2).Value = objPaqueteBE.FechaFinal

            arrSqlParameter(3) = New SqlParameter("@CODEST", System.Data.SqlDbType.VarChar)
            arrSqlParameter(3).Value = objPaqueteBE.Estado

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)


            objListaPaquete = New List(Of PaquetesBE)()

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objPqtBE = New PaquetesBE()
                    objPqtBE.NombrePaquete = dt.Rows(intI).ItemArray(0).ToString
                    objPqtBE.FechaInicio = dt.Rows(intI).ItemArray(1).ToString
                    objPqtBE.FechaFinal = dt.Rows(intI).ItemArray(2).ToString
                    objPqtBE.Estado = dt.Rows(intI).ItemArray(3).ToString
                    intI = intI + 1

                    objListaPaquete.Add(objPqtBE)
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objListaPaquete
    End Function


    Public Function ListaPaqueteBusq(ByVal objPaqueteBE As PaquetesBE) As List(Of PaquetesBE)

        Dim sqlListar As String
        Dim objListaPaquete As New List(Of PaquetesBE)
        Dim objPqtBE As PaquetesBE = Nothing
        Dim arrSqlParameter(1) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try

            sqlListar = "SP_BuscarPaquetesList"

            arrSqlParameter(0) = New SqlParameter("@CODIGO", System.Data.SqlDbType.Int)
            arrSqlParameter(0).Value = objPaqueteBE.codPaquete


            arrSqlParameter(1) = New SqlParameter("@NOMBRE", System.Data.SqlDbType.VarChar)
            arrSqlParameter(1).Value = objPaqueteBE.NombrePaquete


            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)


            objListaPaquete = New List(Of PaquetesBE)()

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objPqtBE = New PaquetesBE()
                    objPqtBE.codPaquete = dt.Rows(intI).ItemArray(0).ToString
                    objPqtBE.NombrePaquete = dt.Rows(intI).ItemArray(1).ToString
                    objPqtBE.FechaInicio = dt.Rows(intI).ItemArray(2).ToString
                    objPqtBE.FechaFinal = dt.Rows(intI).ItemArray(3).ToString

                    intI = intI + 1

                    objListaPaquete.Add(objPqtBE)
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objListaPaquete
    End Function

    Public Function EliminarPaquete(ByVal objPaqueteBE As PaquetesBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(0) As SqlParameter

        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@C_COD_PAQUETE"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objPaqueteBE.codPaquete

            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("SP_PaqueteDel", bExito, sMensaje, arrSqlParameter)

            Return bExito

        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

    End Function

    Public Function ListaPaquete_Update(ByVal objPaqueteParBE As PaquetesBE, ByRef bExito As Boolean, ByRef sMensaje As String) As List(Of PaquetesBE)

        Dim sqlListar As String
        Dim objListaPaquete As New List(Of PaquetesBE)
        Dim objPaqueteBE As PaquetesBE = Nothing
        Dim arrSqlParameter(0) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim intI As Integer = 0

        Try

            sqlListar = "SP_PaqueteUpdate_List"

            arrSqlParameter(0) = New SqlParameter("@CODIGO", System.Data.SqlDbType.Int)
            arrSqlParameter(0).Value = objPaqueteParBE.codSolicitud

            ds = objDatos.DevuelveDataSet(sqlListar, bExito, sMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            objListaPaquete = New List(Of PaquetesBE)()

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objPaqueteBE = New PaquetesBE()
                    objPaqueteBE.codPaquete = dt.Rows(intI).ItemArray(0).ToString
                    objPaqueteBE.codSolicitud = dt.Rows(intI).ItemArray(1).ToString
                    objPaqueteBE.NombrePaquete = dt.Rows(intI).ItemArray(2).ToString
                    objPaqueteBE.InicioVigencia = dt.Rows(intI).ItemArray(3).ToString
                    objPaqueteBE.FinVigencia = dt.Rows(intI).ItemArray(4).ToString
                    objPaqueteBE.Estado = dt.Rows(intI).ItemArray(5).ToString
                    objPaqueteBE.UsuarioCreacion = dt.Rows(intI).ItemArray(6).ToString
                    objPaqueteBE.FechaCreacion = dt.Rows(intI).ItemArray(7).ToString
                    intI = intI + 1

                    objListaPaquete.Add(objPaqueteBE)
                End While
            End If

        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objListaPaquete
    End Function

    Public Function ListaDetallePaquete_Update(ByVal objPaqueteParBE As PaquetesBE, ByRef bExito As Boolean, ByRef sMensaje As String) As List(Of DetallePaqueteBE)

        Dim sqlListar As String
        Dim objListaDetPaquete As New List(Of DetallePaqueteBE)
        Dim objDetPaqueteBE As DetallePaqueteBE = Nothing
        Dim arrSqlParameter(0) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim intI As Integer = 0

        Try

            sqlListar = "SP_PaqueteDetUpdate_List"

            arrSqlParameter(0) = New SqlParameter("@CODIGO", System.Data.SqlDbType.Int)
            arrSqlParameter(0).Value = objPaqueteParBE.codSolicitud

            ds = objDatos.DevuelveDataSet(sqlListar, bExito, sMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            objListaDetPaquete = New List(Of DetallePaqueteBE)()

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objDetPaqueteBE = New DetallePaqueteBE()
                    objDetPaqueteBE.C_COD_PAQUETE = dt.Rows(intI).ItemArray(0).ToString
                    objDetPaqueteBE.C_COD_CIUDAD = dt.Rows(intI).ItemArray(1).ToString
                    objDetPaqueteBE.T_DES_CIUDAD = dt.Rows(intI).ItemArray(2).ToString
                    objDetPaqueteBE.C_COD_PAIS = dt.Rows(intI).ItemArray(3).ToString
                    objDetPaqueteBE.T_DES_PAIS = dt.Rows(intI).ItemArray(4).ToString
                    objDetPaqueteBE.C_COD_SERVICIO = dt.Rows(intI).ItemArray(5).ToString
                    objDetPaqueteBE.T_DES_SERVICIO = dt.Rows(intI).ItemArray(6).ToString
                   
                    intI = intI + 1

                    objListaDetPaquete.Add(objDetPaqueteBE)
                End While
            End If

        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objListaDetPaquete
    End Function

    Public Function ModificaPaquete(ByVal objPaqueteBE As PaquetesBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(6) As SqlParameter

        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@C_COD_PAQUETE"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objPaqueteBE.codPaquete

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@C_COD_SOL_PAQUETE"
            arrSqlParameter(1).SqlDbType = SqlDbType.Int
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objPaqueteBE.codSolicitud

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@T_DESCRIPCION"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Size = 50
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objPaqueteBE.NombrePaquete

            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@FEC_VIGENCIAINI"
            arrSqlParameter(3).SqlDbType = SqlDbType.Date
            arrSqlParameter(3).Direction = ParameterDirection.Input
            arrSqlParameter(3).Value = objPaqueteBE.InicioVigencia

            arrSqlParameter(4) = New SqlParameter()
            arrSqlParameter(4).ParameterName = "@FEC_VIGENCIAFIN"
            arrSqlParameter(4).SqlDbType = SqlDbType.Date
            arrSqlParameter(4).Direction = ParameterDirection.Input
            arrSqlParameter(4).Value = objPaqueteBE.FinVigencia

            arrSqlParameter(5) = New SqlParameter()
            arrSqlParameter(5).ParameterName = "@ESTADO"
            arrSqlParameter(5).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(5).Size = 1
            arrSqlParameter(5).Direction = ParameterDirection.Input
            arrSqlParameter(5).Value = objPaqueteBE.Estado

            arrSqlParameter(6) = New SqlParameter()
            arrSqlParameter(6).ParameterName = "@USUARIO_ACT"
            arrSqlParameter(6).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(6).Size = 10
            arrSqlParameter(6).Direction = ParameterDirection.Input
            arrSqlParameter(6).Value = objPaqueteBE.UsuarioActualiza

            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("SP_PaqueteUpd", bExito, sMensaje, arrSqlParameter)

            Return bExito

        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

    End Function

    Public Function EliminarDetallePaquete(ByVal objPaqueteBE As PaquetesBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(0) As SqlParameter

        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@C_COD_PAQUETE"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objPaqueteBE.codPaquete

            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("SP_PaqueteDetalleDel", bExito, sMensaje, arrSqlParameter)

            Return bExito

        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

    End Function
End Class

